package tracking.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.List;

import tracking.LTEConstants;
import tracking.LogUtil;
import tracking.Utils;
import tracking.dto.Detailsdto;
import tracking.dto.Trackingdto;

public class Trackingdao {
	private static final String TAG = "Trackingdao";
	private Connection conn = null;
	
	/**
	 * Constructor
	 * 
	 * @param conn
	 */
	public Trackingdao(Connection conn) {
		this.conn = conn;
	}
	
	public ResultSet getTrackingByDay(Trackingdto dto){
		ResultSet resultSet = null;
		try {
			int arrange = dto.getArrange();
			String sql = "SELECT * from tracking2 where userid = ? and deviceid = ? and year = ? and month = ? and day = ?";
			if(arrange == LTEConstants.ARRANGE_YES){
				sql = "SELECT * from tracking2 where userid = ? and deviceid = ? and year = ? and month = ? and day = ? order by traffic DESC";
			}
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getUserId());
			ps.setString(2, dto.getDeviceId());
			ps.setInt(3, dto.getYear());
			ps.setInt(4, dto.getMonth());
			ps.setInt(5, dto.getDay());
			
			resultSet = ps.executeQuery();
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDay:" + e.toString());
		}
		return resultSet;
	}
	
	public ResultSet getTrackingByMonth(Trackingdto dto){
		ResultSet resultSet = null;
		try {
			String sql = "SELECT * from tracking2 where userid = ? and deviceid = ? and year = ? and month = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getUserId());
			ps.setString(2, dto.getDeviceId());
			ps.setInt(3, dto.getYear());
			ps.setInt(4, dto.getMonth());
			
			resultSet = ps.executeQuery();
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByMonth:" + e.toString());
		}
		return resultSet;
	}
	
	public ResultSet getTrackingByYear(Trackingdto dto){
		ResultSet resultSet = null;
		try {
			String sql = "SELECT * from tracking2 where userid = ? and deviceid = ? and year = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getUserId());
			ps.setString(2, dto.getDeviceId());
			ps.setInt(3, dto.getYear());
			resultSet = ps.executeQuery();
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByYear:" + e.toString());
		}
		return resultSet;
	}
	
	public ResultSet getDetails(String detailsId){
		ResultSet resultSet = null;
		try {
			String sql = "SELECT * from details2 where detailsId = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, detailsId);
			resultSet = ps.executeQuery();
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDay:" + e.toString());
		}
		return resultSet;
	}
	
	public ResultSet getDetails(String detailsId, int arrange){
		ResultSet resultSet = null;
		try {
			String sql = "SELECT * from details2 where detailsId = ? order by lastupdate DESC";
			if(arrange == LTEConstants.ARRANGE_YES){
				sql = "SELECT * from details2 where detailsId = ? order by traffic DESC";
			}
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, detailsId);
			resultSet = ps.executeQuery();
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDay:" + e.toString());
		}
		return resultSet;
	}
	
	//get all deviceid
	public ResultSet getDeviceId(String userId){
		ResultSet resultSet = null;
		try {
			String sql = "SELECT distinct deviceid from tracking2 where userid=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, userId);
			resultSet = ps.executeQuery();
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDay:" + e.toString());
		}
		return resultSet;
	}
	
	public int insertTracking(Trackingdto dto){
		try {
			String sql;
			int result;
			if(isDetailsIsExistInTracking(dto)){
				sql = "update tracking set traffic = ? where detailsid = ?";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, dto.getTrafficSum());
				ps.setString(2, dto.getDetailsID());
				result  = ps.executeUpdate();
				LogUtil.log(TAG, "update tracking result:" + result);
			}else{
				sql = "insert into tracking value (?, ?, ?, ?,?,?,?)";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, dto.getDetailsID());
				ps.setString(2, dto.getUserId());
				ps.setString(3, dto.getDeviceId());
				ps.setInt(4, dto.getYear());
				ps.setInt(5, dto.getMonth());
				ps.setInt(6, dto.getDay());
				ps.setInt(7, dto.getTrafficSum());
				result  = ps.executeUpdate();
				LogUtil.log(TAG, "insertTracking result:" + result);
				
			}
			return result;
			
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDay:" + e.toString());
			return -1;
		}
	}
	
	public int insertTracking2(Trackingdto dto, Detailsdto detailsdto){
		LogUtil.log(TAG, "*****insertTracking2******");
		try {
			String sql;
			int result;
//			String detailsId = String.valueOf(detailsdto.getLongtime());
			String detailsId = Utils.getDetailsId(detailsdto.getLongtime());
			if(isDetailsIsExistInTracking2(detailsId)){
				sql = "update tracking2 set traffic = ? where detailsid = ?";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, dto.getTrafficSum());
				ps.setString(2, detailsId);
				result  = ps.executeUpdate();
				LogUtil.log(TAG, "update tracking result:" + result);
			}else{
				sql = "insert into tracking2 value (?, ?, ?, ?,?,?,?,?)";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1,detailsId);
				ps.setString(2, dto.getUserId());
				ps.setString(3, dto.getDeviceId());
				Calendar c = Calendar.getInstance();
				c.setTimeInMillis(detailsdto.getLongtime());
				int year = c.get(Calendar.YEAR);
				int month = c.get(Calendar.MONTH) + 1;
				int day = c.get(Calendar.DAY_OF_MONTH);
				int hour = c.get(Calendar.HOUR_OF_DAY);
				
				ps.setInt(4, year);
				ps.setInt(5, month);
				ps.setInt(6, day);
				ps.setInt(7, hour);
				ps.setInt(8, dto.getTrafficSum());
				result  = ps.executeUpdate();
				LogUtil.log(TAG, "insertTracking result:" + result);
				
			}
			return result;
			
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDay:" + e.toString());
			return -1;
		}
	}
	
	public boolean isDetailsIsExistInTracking(Trackingdto dto){
		try {
			String sql = "select detailsId from tracking2 where detailsid = ?";
			LogUtil.log(TAG, "sql");
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getDetailsID()); 
			 
			ResultSet resultSet  = ps.executeQuery();
			if(resultSet == null){
				return false;
			}
			if(resultSet.first()){
				return true;
			}
			return false;
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDay:" + e.toString());
			return false;
		}
	}
	
	public boolean isDetailsIsExistInTracking2(String detailsId){
		try {
			String sql = "select detailsId from tracking2 where detailsid = ?";
			LogUtil.log(TAG, "sql");
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, detailsId); 
			 
			ResultSet resultSet  = ps.executeQuery();
			if(resultSet == null){
				return false;
			}
			if(resultSet.first()){
				return true;
			}
			return false;
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDay:" + e.toString());
			return false;
		}
	}
	
	public int insertDetails(Trackingdto dto){
		LogUtil.log(TAG, "****insertDetails*****");
		int result = 0;
		try {
			String sql;
			
			List<Detailsdto> list = dto.getList();
			for (Detailsdto detailsdto : list) {
//				String detailsId = String.valueOf(detailsdto.getLongtime());
				String detailsId = Utils.getDetailsId(detailsdto.getLongtime());
				
				if(isUpdateDetails(detailsdto)){
					LogUtil.log(TAG, "update details");
					sql = "update details2 set traffic= ?, lastupdate =?  where detailsid = ? and appname=?";
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setInt(1, detailsdto.getTraffic());
//					String time = Utils.getTimeUpdateDB(detailsdto.getLongtime());
					ps.setString(2,detailsdto.getTime());
					ps.setString(3, detailsId);
					ps.setString(4, detailsdto.getAppname());
					result  = ps.executeUpdate();
					LogUtil.log(TAG, "update Details result:" + result);
				}else{
					LogUtil.log(TAG, "Insert details");
					sql = "insert into details2 value (?, ?, ?, ?,?)";
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setString(1, detailsId);
					ps.setString(2, detailsdto.getAppname());
					ps.setString(3, detailsdto.getAppPackage());
					ps.setInt(4, detailsdto.getTraffic());
//					String time = Utils.getTimeUpdateDB(detailsdto.getLongtime());
					ps.setString(5,detailsdto.getTime());
					result  = ps.executeUpdate();
					LogUtil.log(TAG, "insert Details result:" + result);
				}
				
			}
			return result;
			
		} catch (Exception e) {
			LogUtil.log(TAG, "error insertDetails:" + e.toString());
			return -1;
		}
	}
	
	public boolean isUpdateDetails(Detailsdto dto, String detailsId){
		try {
			String sql = "select * from details2 where detailsid = ? and appname = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, detailsId); 
			ps.setString(2, dto.getAppname()); 
			 
			ResultSet resultSet  = ps.executeQuery();
			if(resultSet == null){
				return false;
			}
			if(resultSet.first()){
				return true;
			}
			return false;
		} catch (Exception e) {
			LogUtil.log(TAG, "error isUpdateDetails:" + e.toString());
			return false;
		}
	}
	
	public boolean isUpdateDetails(Detailsdto dto){
		try {
			String sql = "select * from details2 where detailsid = ? and appname = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
//			String detailsId = String.valueOf(dto.getLongtime());
			String detailsId = Utils.getDetailsId(dto.getLongtime());
			ps.setString(1, detailsId); 
			ps.setString(2, dto.getAppname()); 
			 
			ResultSet resultSet  = ps.executeQuery();
			if(resultSet == null){
				return false;
			}
			if(resultSet.first()){
				return true;
			}
			return false;
		} catch (Exception e) {
			LogUtil.log(TAG, "error isUpdateDetails:" + e.toString());
			return false;
		}
	}
	
	public ResultSet getTrackingByDayToDay(Trackingdto dto){
		LogUtil.log(TAG, ">>>getTrackingByDayToDay");
		ResultSet resultSet = null;
		try {
			String sql = "SELECT * from tracking2 where userid = ? and deviceid = ? and detailsid between ? and ? order by detailsid ASC";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getUserId());
			ps.setString(2, dto.getDeviceId());
			ps.setString(3, dto.getStart());
			ps.setString(4, dto.getEnd() + " 23:59:59");
			
			resultSet = ps.executeQuery();
			LogUtil.log(TAG, "resultSet:" + resultSet.getRow());
		} catch (Exception e) {
			LogUtil.log(TAG, "error getTrackingByDayToDay:" + e.toString());
		}
		return resultSet;
	}
}
